﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace Fwk.Datos
{

    public class Entidad
    {
        String ConString = String.Empty;
        String Key = String.Empty;
        SqlConnection con = new SqlConnection();
        SqlCommand olecom = new SqlCommand();
        public Entidad()
        {
            //Conexion con la base de datos
            ConString = "Data Source= 'GABO\\SQLEXPRESS';";
            ConString += "User ID= 'con';";
            ConString += "Password= '111';";
            ConString += "Initial Catalog=DB_SEG";
 
        }

        private DataSet dsEstructura;

        //
        public DataSet Estructura
        {
            get { return dsEstructura; }
            set
            {
                dsEstructura = value;
                Key = dsEstructura.Tables[0].Columns[0].ColumnName;
            }
        }

        public void Guardar(DataSet ds)
        {
            DataTable dt = ds.Tables[0];

            foreach (DataRow oRow in dt.Rows)
            {
                if (oRow.RowState == DataRowState.Deleted)
                {
                    Eliminar(oRow);
                }
            }

            foreach (DataRow oRow in dt.Rows)
            {
                if (oRow.RowState == DataRowState.Added)
                {
                    Insertar(oRow);
                }
                else if (oRow.RowState == DataRowState.Modified)
                {
                    Modificar(oRow);
                }
            }

        }

        private void Insertar(DataRow row)
        {
            Ejecutar(GenerarSPIntert(row));
        }

        private void Modificar(DataRow row)
        {
            Ejecutar(GenerarSPUpdate(row));
        }

        private void Eliminar(DataRow row)
        {
            Ejecutar(GenerarSPEliminar(row));

        }

     
        private void Ejecutar(String sp)
        {

            con = new SqlConnection(ConString);
            con.Open();

            olecom = con.CreateCommand();
            olecom.CommandText = sp;
            olecom.ExecuteNonQuery();

            con.Close();

        }

        private String GenerarSPIntert(DataRow row)
        {
            DataTable dt = dsEstructura.Tables[0];
            //hace el stored procedure
            String spIntert = "INSERT INTO " + dt.TableName + " ({0}) VALUES ({1})";
            String Columnas = String.Empty;
            String Valores = String.Empty;
            //recorro columnas de la tabla y genera el sp de insert
            foreach (DataColumn col in dt.Columns)
            {
                if (!col.ReadOnly)
                {
                    if (row[col.ColumnName].GetType() != typeof(System.DBNull))
                    {
                        Columnas += col.ColumnName + ", ";

                        if (row[col.ColumnName].GetType() == typeof(Int32))
                        {
                            Valores += row[col.ColumnName] + ", ";
                        }
                        else
                        {
                            Valores += "'" + row[col.ColumnName] + "', ";
                        }
                    }

                }
            }

            Columnas = Columnas.Substring(0, Columnas.Length - 2);
            Valores = Valores.Substring(0, Valores.Length - 2);

            String spIntertComplete = String.Format(spIntert, Columnas, Valores);

            return spIntertComplete;
        }

        private String GenerarSPUpdate(DataRow row)
        {
            DataTable dt = dsEstructura.Tables[0];

            String spUpdate = "UPDATE " + dt.TableName + " SET {0} WHERE {1}";
            String update = String.Empty;
            foreach (DataColumn col in dt.Columns)
            {
                if (!col.ReadOnly)
                {
                    if (col.ColumnName != Key)
                    {
                        if (row[col.ColumnName].GetType() != typeof(System.DBNull))
                        {
                            if (row[col.ColumnName].GetType() == typeof(Int32))
                            {
                                update += col.ColumnName + " = " + row[col.ColumnName] + ", ";
                            }
                            else
                            {
                                update += col.ColumnName + " = '" + row[col.ColumnName] + "', ";
                            }
                        }
                    }
                }
            }

            update = update.Substring(0, update.Length - 2);

            String condicion = Key + " = '{" + row[Key].ToString() + "}'";

            String spUpdateComplete = String.Format(spUpdate, update, condicion);
            return spUpdateComplete;
        }

        private String GenerarSPSelect(DataSet ds, String ColumnName, Object value)
        {
            DataTable dt = dsEstructura.Tables[0];

            String spSelect = "SELECT {0} FROM " + dt.TableName + " WHERE {1}";
            String select = String.Empty;
            foreach (DataColumn col in dt.Columns)
            {
                select += col.ColumnName + ", ";
            }

            select = select.Substring(0, select.Length - 2);

            String spSelectComplete = String.Empty;

            if (!String.IsNullOrEmpty(value.ToString()))
            {
                String condicion = String.Empty;
                if (value.GetType() == (typeof(String)))
                {
                    condicion = ColumnName + " LIKE '" + value.ToString() + "%'";
                }
                else if (value.GetType() == (typeof(Guid)))
                {
                    condicion = ColumnName + " = '" + value.ToString() + "'";
                }
                else
                {
                    condicion = ColumnName + " = " + value.ToString();
                }
                spSelectComplete = String.Format(spSelect, select, condicion);
            }
            else
            {
                spSelect = "SELECT {0} FROM " + dt.TableName;
                spSelectComplete = String.Format(spSelect, select);

            }

            return spSelectComplete;
        }

        private String GenerarSPEliminar(DataRow row)
        {
            DataTable dt = dsEstructura.Tables[0];
            row.RejectChanges();
            String spEliminar = "DELETE FROM " + dt.TableName + " WHERE {0}";

            String condicion = Key + " = '" + row[Key].ToString() + "'";

            String spEliminarComplete = String.Format(spEliminar, condicion);
            return spEliminarComplete;
        }

        public void Leer(DataSet ds)
        {

            LeerDatos(ds, GenerarSPSelect(ds, String.Empty, String.Empty));
        }

        public void Leer(DataSet ds, Int32 ID)
        {
            LeerDatos(ds, GenerarSPSelect(ds, Key, ID));
        }

        public void Leer(DataSet ds, Guid ID)
        {
            LeerDatos(ds, GenerarSPSelect(ds, Key, ID));
        }

        public void Leer(DataSet ds, DataColumn col, Object value)
        {
            LeerDatos(ds, GenerarSPSelect(ds, col.ColumnName, value));
        }

        public void Leer(DataSet ds, String sp)
        {
            LeerDatos(ds, sp);
        }

        private void LeerDatos(DataSet ds, String sp)
        {
            con = new SqlConnection(ConString);
            con.Open();
            olecom = con.CreateCommand();
            olecom.CommandText = sp;
            SqlDataReader oledr = olecom.ExecuteReader();
            foreach (DataTable tb in ds.Tables)
            {
                tb.BeginLoadData();
                Int32 Columnas = tb.Columns.Count;
                while (oledr.Read())
                {
                    DataRow row = tb.NewRow();
                    for (int i = 0; i < Columnas; i++)
                    {
                        row[i] = oledr.GetValue(i);
                    }
                    tb.Rows.Add(row);
                }
                tb.EndLoadData();
            }

            ds.AcceptChanges();
        }

    }
}
